<?php
//Get Branch Information
mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_branch = "SELECT * FROM branch WHERE branch.id='".$_REQUEST['branch_id']."'";
$branch = mysql_query($query_branch, $drycleaners_conn) or die(mysql_error());
$row_branch = mysql_fetch_assoc($branch);
$totalRows_branch = mysql_num_rows($branch);

//Get Order Information of A given Branch
mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order = "SELECT sum(order_payment.amount) as total,
       `order`.id,
       person.first_name,
       person.last_name,
       `order`.order_no
  FROM    (   (   drycleaners.customer customer
               INNER JOIN
                  drycleaners.person person
               ON (customer.person_id = person.id))
           INNER JOIN
              drycleaners.`order` `order`
           ON (`order`.customer_id = customer.id))
       LEFT OUTER JOIN
          drycleaners.order_payment order_payment
       ON (order_payment.order_id = `order`.id)
 WHERE  order.created_date BETWEEN '".$_REQUEST['from_date']." 00:00:00"."' AND '".$_REQUEST['to_date']." 23:00:00"."'  AND order.branch_id='".$_REQUEST['branch_id']."'
 Group By `order`.id
 ";
$order = mysql_query($query_order, $drycleaners_conn);
$row_order = mysql_fetch_assoc($order);
$totalRows_order = mysql_num_rows($order);

//Determine if the Is set for exports 
if(isset($_REQUEST['branch_report_exporter'])){
//Report Formart Settings
	header( "Content-Type: application/vnd.ms-excel" );
	header( "Content-disposition: attachment; filename=".$row_branch['name']."_".date("d-M-Y",strtotime($_REQUEST['from_date']))."-".date("d-M-Y",strtotime($_REQUEST['to_date'])).".xls" );	
	header("Pragma: no-cache");
	header("Expires: 0");

//Print Header
echo 'Branch Name:'."\t".$row_branch['name']."\t".'Report Range'."\t"."From: ".date("d-M-Y",strtotime($_REQUEST['from_date']))."\t"."To: ".date("d-M-Y",strtotime($_REQUEST['to_date']))."\n";
 echo 'Order No'."\t".'Customer Names'."\t".'Bill'."\t".'Paid'."\t".'Balance'."\t".'Customer Advance CF'."\n";
 
$branch_total_bill=0;
$branch_total_payment=0;
//Loop of printing out order Transaction details
do{
  $orderNo= $row_order["order_no"]; 
  $customer=$row_order["first_name"]." ".$row_order["last_name"];
  $total_bill=0;
  
 mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_item_by_order_id = sprintf("SELECT material.name as material_name,
       item.name as item_name,
       type_of_service.name as type_of_service_name,
       order_item.color,
	   order_item.id,
	   order_item.material_id,
	   order_item.item_id
  FROM    (   (   drycleaners.order_item order_item
               INNER JOIN
                  drycleaners.type_of_service type_of_service
               ON (order_item.type_of_service_id = type_of_service.id))
           INNER JOIN
              drycleaners.item item
           ON (order_item.item_id = item.id))
       INNER JOIN
          drycleaners.material material
       ON (order_item.material_id = material.id)
	   WHERE
	    order_id =%s", GetSQLValueString($row_order['id'], "int"));
$order_item_by_order_id = mysql_query($query_order_item_by_order_id, $drycleaners_conn);
$row_order_item_by_order_id = mysql_fetch_assoc($order_item_by_order_id);

//Loop Determing Cost Per Item In the Order
do{
$cost=0;
	mysql_select_db($database_drycleaners_conn, $drycleaners_conn);
$query_order_item_service = "SELECT order_item_service.id, service.name as service_name,order_item_service.service_id
  FROM    drycleaners.order_item_service order_item_service
       INNER JOIN
          drycleaners.service service
       ON (order_item_service.service_id = service.id) WHERE order_item_id='".$row_order_item_by_order_id['id']."'";   
$order_item_service = mysql_query($query_order_item_service, $drycleaners_conn);
$row_order_item_service = mysql_fetch_assoc($order_item_service);
// Loop Determining The Service Costs	
	do{
		$query_prices="SELECT material_service_cost.price, material_service_cost.express_extra_price
  FROM drycleaners.material_service_cost material_service_cost Where material_id='".$row_order_item_by_order_id['material_id']."' AND item_id='".$row_order_item_by_order_id['item_id']."' AND service_id='".$row_order_item_service['service_id']."'";
  $get_prices=mysql_query($query_prices,$drycleaners_conn);
  $row_get_prices = mysql_fetch_assoc($get_prices);
	   if($row_order_item_by_order_id["name"]='express'){
	   $service_cost=$row_get_prices['price']+$row_get_prices['express_extra_price'];
	   }
	   else{
		   $service_cost=$row_get_prices['price'];
		   }
		   $cost=$cost+$service_cost;
		}while($row_order_item_service=mysql_fetch_assoc($order_item_service));
	$total_bill=$total_bill+$cost;
	}while($row_order_item_by_order_id=mysql_fetch_assoc($order_item_by_order_id));
  if($total_bill-$row_order['total']>=0){
	  								$total_balance=($total_bill-$row_order['total']);}
	  								else{$total_balance=0;}
									
if($row_order['total']-$total_bill>=0){
	  								$total_advance=($row_order['total']-$total_bill);}
	  								else{$total_advance=0;}
 //Printing out The Order Transaction Details 
 echo $orderNo."\t".$customer."\t".$total_bill."\t".$row_order['total']."\t".$total_balance."\t".$total_advance."\n"; 

mysql_free_result($order_item_by_order_id);
mysql_free_result($order_item_service);

$branch_total_bill=$branch_total_bill+$total_bill;
$branch_total_payment=$branch_total_payment+$row_order['total'];

 }while($row_order=mysql_fetch_assoc($order));
 //Printing out the over all totals
 echo "Total"."\t \t".$branch_total_bill."\t".$branch_total_payment."\t"."=sum(E5:E".((5+$totalRows_order)-1).")"."\t"."=sum(F5:F".((5+$totalRows_order)-1).")"."\n"; 
}


mysql_free_result($order);

mysql_free_result($branch);
?>
